<?php

namespace app\common\model;

use think\Model;
use app\common\controller\Tools;
use app\common\tool\SFDeliver;

class Book extends Model
{
    protected $table = 'zj_book';
    protected $pk = 'id';
    protected $autoWriteTimestamp = true;
    protected $createTime = 'create_time';
    protected $updateTime = 'update_time';

    // 验证规则
    protected $rule = [
        ['id|图书id', ['require']],
    ];

    // 验证场景
    protected $scene = [
        'update' => ['id'],
        'create' => ['id']
    ];

    // 验证器
    public function intValidate()
    {
        $validate = new \think\Validate($this->rule);
        foreach ($this->scene as $k1 => $v1)
            $validate->scene($k1, $v1);
        return $validate;
    }

    /**
     * 获取图书列表
     */
    public function getBookList($merchant_id,$page = 10,$status = 0){
        $where['b.merchant_id'] = ['eq', $merchant_id];
        $where['b.status'] = ['neq', 9];
        //标题关键字筛选
        $keyword = input('param.keyword', '');
        if ($keyword) {
            $where['b.title|b.series|b.isbn13'] = ['like', "%$keyword%"];
        }
        //创建时间筛选
        $begintime = input('param.begintime', '');
        $endtime = input('param.endtime', '');
        if ($begintime && $endtime){
            $where['b.create_time'] = ['between', [strtotime("$begintime 00:00:00"), strtotime("$endtime 23:59:59")]];
        }else if($begintime && empty($endtime)){
            $where['b.create_time'] = ['egt', strtotime("$begintime 00:00:00")];
        }else if(empty($begintime) && $endtime){
            $where['b.create_time'] = ['elt', strtotime("$endtime 23:59:59")];
        }
        if($status > 0){
            $where['b.status'] = ['eq', $status];
        }
        $book = new Book();
        $book_list = $book::alias("b")
            ->field("b.id,b.title,b.stock,b.img_medium,b.price,b.discount,b.img_main,b.status,b.series,b.create_time,author,min_age,max_age,publisher,summary")
            ->group("b.id")
            ->where($where)
            ->order("status asc,creater asc,b.id desc")
            ->paginate($page,false,
                array('query' => array('type' => input('type'),
                                        'keyword' => input('keyword'),
                                        'begintime' => input('begintime'),
                                        'endtime' => input('endtime')
                )));
        return $book_list;
    }

    /**
     * 添加图书
     */
    public function addBookInfo($data){
        $book = new Book();
        $res = $book->data($data)->save();
        $stock_info = [
            'book_id' => $book->id,
            'merchant_id' => session('merchant.merchant_id'),
            'type' => 1,
            'create_time' => time(),
            'stock_add' => $data['stock'],
            'total_stock' => $data['stock'],
            'surplus_stock' => $data['stock']
        ];
        Db("zj_stock_history")->insert($stock_info);
        return $book->id;
    }

    /**
     * 修改图书
     */
    public function updateBookInfo($bookid,$data){
        $stock_flash = self::handleStock($bookid,$data['stock']);
        $book = new Book();
        $res = $book->where("id = $bookid")->update($data);
        return $res;
    }

    /**
     * 绑定图书类目标签
     */
    public function bindBookCategoryTag($bookId,$category){
        $data = [];
        $category = explode(",",$category);
        foreach ($category as $k => $v){
            $data[] = [
                'book_id' => $bookId,
                'category_id' => $v,
                'create_time' => time(),
                'update_time' => time(),
            ];
        }
        Db("zj_book_category_list")->where("book_id = $bookId")->delete();
        $res = Db("zj_book_category_list")->insertAll($data);
        return $res;
    }

    /**
     * 上架图书
     */
    public function onBook($id){
        $book = new Book();
        $cover =  $book::where("id = $id")->value("img_medium");
        if(empty($cover)){
            return false;
        }
        $res = $book::where("id = $id")->update(['status'=>2]);
        return $res;
    }

    /**
     * 下架图书
     */
    public function downBook($id){
        $book = new Book();
        $res = $book::where("id = $id")->update(['status'=>3]);
        return $res;
    }

    /**
     * 删除图书
     */
    public function delBook($id){
        $book = new Book();
        $res = $book::where("id = $id")->update(['status'=>9]);
        //$res = Db("zj_book_category_list")->where("book_id = $id")->delete();
        return $res;
    }

    /**
     * 获取店铺信息
     */
    public function getStoreInfo($merchant_id){
        $storeinfo = Db("store")
            ->field("store_id,store_name")
            ->where("store_verify_status = 2 and store_is_forever_close = 0 and store_is_close = 0 and store_is_delete = 0 ")
            ->where("store_merchant_id = $merchant_id")
            ->find();
        return $storeinfo;
    }

    /**
     * 获取图书信息
     */
    public function getBookInfo($bookid){
        $book = new Book();
        $book_info = $book::where("id = $bookid")->find();
        return $book_info;
    }

    /**
     * 获取图书信息
     */
    public function getSeriesData($bookid){
        $merchant_id = session('merchant.merchant_id');
        $book = new Book();
        $book_info = $book::where("id < $bookid and merchant_id = $merchant_id and status <> 9")->order("id desc")->find();
        $book_this= $book::where("id = $bookid and merchant_id = $merchant_id")->order("id desc")->find();
        $update_data = [
            'pub_date'=>$book_this['pub_date'],
            'author'=>$book_this['author'],//$book_info['author']
            'publisher'=>$book_this['publisher'],//$book_info['publisher']
            'price'=>$book_this['price'],
            'discount'=>100,
            'stock'=>$book_info['stock'],
            'surplus_stock'=>$book_info['stock'],
            'revert_id'=>$book_info['revert_id'],
            'merchant_id'=>$book_info['merchant_id'],
            'store_id'=>$book_info['store_id'],
            'least_advance_days'=>$book_info['least_advance_days'],
            'max_advance_days'=>$book_info['max_advance_days'],
            'series'=>$book_info['series'],
            'min_age'=>$book_info['min_age'],
            'max_age'=>$book_info['max_age'],
            'language'=>$book_info['language'],
        ];
        $stock_flash = self::handleStock($bookid,$update_data['stock']);
        $result = $book::where("id = $bookid and merchant_id = $merchant_id")->update($update_data);
        $data = [];
        $category = Db("zj_book_category_list")->where("book_id = {$book_info['id']}")->value("group_concat(category_id)");
        $category = explode(",",$category);
        foreach ($category as $k => $v){
            $data[] = [
                'book_id' => $bookid,
                'category_id' => $v,
                'create_time' => time(),
                'update_time' => time(),
            ];
        }
        Db("zj_book_category_list")->where("book_id = $bookid")->delete();
        $res = Db("zj_book_category_list")->insertAll($data);
        return $res;
    }

    /**
     * 获取所有年龄标签
     */
    public function getAgeList(){
        $age_list = Db("zj_book_age")
            ->field("id,name,sort")
            ->where("status = 1")
            ->select();
        return $age_list;
    }

    /**
     * 获取所有类目标签
     */
    public function getCategoryList(){
        $category_list = Db("zj_book_category")
            ->field("id,name,sort")
            ->where("status = 1")
            ->order("sort desc")
            ->select();
        return $category_list;
    }

    /**
     * 获取图书的所有年龄标签
     */
    public function getBookAgeList($bookid){
        $book_age_list = Db("zj_book_age")->alias("ba")
            ->join("zj_book_age_list bal","ba.id = bal.age_id",'left')
            ->field("ba.id,ba.name,ba.sort")
            ->where("ba.status = 1 and bal.book_id = $bookid")
            ->select();
        return $book_age_list;
    }

    /**
     * 获取图书的所有类目标签
     */
    public function getBookCategoryList($bookid){
        $book_category_list = Db("zj_book_category")->alias("bc")
            ->join("zj_book_category_list bcl","bc.id = bcl.category_id",'left')
            ->field("bc.id,bc.name,bc.sort")
            ->where("bc.status = 1 and bcl.book_id = $bookid")
            ->select();
        return $book_category_list;
    }

    /**
     * 处理库存
     */
    public function handleStock($bookid,$stock){
        $merchant_id = session('merchant.merchant_id');
        $old_stock = Db("zj_book")->field("stock,surplus_stock")->where("id = $bookid")->find();
        $old_stock = empty($old_stock) ? ['stock'=>0,'surplus_stock'=>0] : $old_stock;
        if(intval($old_stock['stock']) == intval($stock)){
            return true;
        }
        $stock_data = [
            'book_id' => $bookid,
            'merchant_id' => $merchant_id,
            'type' => 1,
            'create_time' => time()
        ];
        if(intval($old_stock['stock']) < intval($stock)){
            $stock_data['stock_add'] = intval($stock) - intval($old_stock['stock']);
            $stock_data['total_stock'] = intval($stock);
            $stock_data['surplus_stock'] = intval($old_stock['surplus_stock'])+intval($stock) - intval($old_stock['stock']);
        }else{
            $stock_data['stock_sub'] = intval($old_stock['stock']) - intval($stock);
            $stock_data['total_stock'] = intval($stock);
            $stock_data['surplus_stock'] = intval($old_stock['surplus_stock'])-(intval($old_stock['stock']) - intval($stock));
        }
        $res = Db("zj_stock_history")->insert($stock_data);
        $res = Db("zj_book")->where("id = $bookid")->update(['stock'=>$stock_data['total_stock'],'surplus_stock'=>$stock_data['surplus_stock']]);
        return $res;
    }
    /**
     * 获取图书的分类
     */
    public function getBookClass(){
        $book_class_list = Db('zj_book_type')->where('status',1)->where('type',1)->select();
        return $book_class_list;
    }
    /**
     * 获取图书的类型
     */
    public function getBookType(){
        $book_class_list = Db('zj_book_type')->where('status',1)->where('type',2)->select();
        return $book_class_list;
    }
    /** ------------------------------------------------------------------图书处理(上)--------------------------------------------------------------------- **/
    /** ------------------------------------------------------------------图书订单(下)--------------------------------------------------------------------- **/

    /**
     * 获取图书订单列表
     */
    public function getBorrowOrderList($page = 10,$where = []){
        $keyword = input("keyword/s",'');
        $keyword_end = input("keyword_end/s");
		$orderstr='bo.id desc';
		$field = '';
		switch(input('type'))
		{
			case '1':
			 $orderstr='bo.id desc';
			break;
			case '2':
                $orderstr='bo.rent_start_time asc,bo.id asc';
                break;
			case '3':
            $field=",from_unixtime(got.order_shipping_time,'%Y-%m-%d') as shipping_time";
                $orderstr='got.order_shipping_time desc';
            break;
			case '4':
			$orderstr='bo.rent_start_time asc,bo.id asc';
			break;
			case '5':
            $orderstr='got.order_settlement_bill_generation_time desc,rent_start_time desc';
            $field=",from_unixtime(got.order_settlement_bill_generation_time,'%Y-%m-%d') as settlement_time";
			break;
            case '6':
            $orderstr='rent_start_time asc';
            break;
            case '7':
                $orderstr='cancel_time desc';
                break;
			default:
			$orderstr='bo.id desc';
			break;
		}
        if(!empty($keyword)){
            $where['ua.address_name|bo.order_sn|u.user_phone|bo.remark|gor.return_expressid'] = ['like', "%$keyword%"];
        }
        if(!empty($keyword_end)) {
            $where['zeo.express_no'] = ['eq',"$keyword_end"];
            $borrow_order_list = Db("zj_book_order")->alias("bo")
                ->field("bo.id,bo.is_renew,bo.order_sn,bo.remark,bo.user_card_id,bo.address as address_snapshot,bo.address_id,bo.pay_type,bo.status,u.user_phone,vc.card_no,c.type,c.rank,bo.book_num,
                from_unixtime(bo.rent_start_time,'%Y-%m-%d') as rent_start_time,
                from_unixtime(bo.rent_end_time,'%Y-%m-%d') as rent_end_time,
                from_unixtime(bo.cancel_time,'%Y-%m-%d') as cancel_time,
                from_unixtime(bo.create_time,'%Y-%m-%d') as create_time,ua.address_name".$field)
                ->join("zj_user_card vc","bo.user_card_id = vc.id",'left')
                ->join("zj_card c","vc.card_id = c.id",'left')
                ->join("user u","bo.user_id = u.user_id",'left')
                ->join("user_address ua","bo.address_id = ua.id",'left')
                ->join("goods_order_return gor","gor.book_order_id = bo.id",'left')
                ->join("goods_order_time got","got.book_order_id = bo.id",'left')
                ->join("zj_express_order zeo","zeo.book_order_id = bo.id",'left')
                ->where($where)
                ->group("bo.id")
                ->order($orderstr)
                ->paginate($page, false, [
                    'query' => request()->param(),
                ]);
        }else{
            $borrow_order_list = Db("zj_book_order")->alias("bo")
                ->field("bo.id,bo.is_renew,bo.order_sn,bo.remark,bo.user_card_id,bo.address as address_snapshot,bo.address_id,bo.pay_type,bo.status,u.user_phone,vc.card_no,c.type,c.rank,bo.book_num,
                from_unixtime(bo.rent_start_time,'%Y-%m-%d') as rent_start_time,
                from_unixtime(bo.rent_end_time,'%Y-%m-%d') as rent_end_time,
                from_unixtime(bo.cancel_time,'%Y-%m-%d') as cancel_time,
                from_unixtime(bo.create_time,'%Y-%m-%d') as create_time,ua.address_name".$field)
                ->join("zj_user_card vc","bo.user_card_id = vc.id",'left')
                ->join("zj_card c","vc.card_id = c.id",'left')
                ->join("user u","bo.user_id = u.user_id",'left')
                ->join("user_address ua","bo.address_id = ua.id",'left')
                ->join("goods_order_return gor","gor.book_order_id = bo.id",'left')
                ->join("goods_order_time got","got.book_order_id = bo.id",'left')
                ->where($where)
                ->group("bo.id")
                ->order($orderstr)
                ->paginate($page, false, [
                    'query' => request()->param(),
                ]);
        }
        
        return $borrow_order_list;
    }

    /**
     * 获取图书订单状态记数
     */
    public function borrowOrderCount($where = []){
        $borrow_order_count = Db("zj_book_order")->alias("bo")
            ->where($where)
            ->group("bo.id")
            ->count();
        return $borrow_order_count;
    }

    /**
     * 获取待发货订单信息
     */
    public function getDeliverOrderInfo($order_id){
        $deliver_order_info = Db("zj_book_order")->alias("bo")
            ->field("bo.id,bo.user_message,bo.create_time,bo.address_id,bo.address as address_snapshot,bo.pay_type,bo.rent_start_time,bo.rent_end_time,u.user_phone,bo.book_num,
            ua.address_name,ua.province,ua.city,ua.district,ua.address,ua.mobile,vc.card_no,c.type,c.rank")
            ->join("user_address ua","bo.address_id = ua.id",'left')
            ->join("zj_user_card vc","bo.user_card_id = vc.id",'left')
            ->join("zj_card c","vc.card_id = c.id",'left')
            ->join("user u","bo.user_id = u.user_id",'left')
            ->where("bo.id = $order_id")
            ->find();
        if(!empty($deliver_order_info['address_snapshot'])){
            $address_info = json_decode($deliver_order_info['address_snapshot'],true);
            $deliver_order_info['address_name'] = $address_info['address_name'];
            $deliver_order_info['province'] = $address_info['province'];
            $deliver_order_info['city'] = $address_info['city'];
            $deliver_order_info['district'] = $address_info['district'];
            $deliver_order_info['address'] = $address_info['address'];
            $deliver_order_info['mobile'] = $address_info['mobile'];
        }
        $books_id = Db("zj_book_order_list")->where("order_id = $order_id")->value("group_concat(book_id)");
        $book_list = Db("zj_book")->field("id,img_medium,title,publisher,isbn13")->where("id in ($books_id)")
            ->order("creater asc,convert(publisher USING gbk) COLLATE gbk_chinese_ci")->select();
        $deliver_order_info['book_list'] = $book_list;
        $mail_no = Db("goods_order_deliver")->where("book_order_id = $order_id")->value('order_deliver_expressid');
        $deliver_order_info['mail_no'] = $mail_no;
        return $deliver_order_info;
    }

    /**
     * 添加图书订单发货信息
     */
    public function saveDeliverInfo($data = []){
        $merchant_id = session('merchant.merchant_id');
        $res = Db("goods_order_deliver")->insert([
            'order_id'=>'',
            'book_order_id'=>$data['order_id'],
            'order_express_type'=>1,
            'order_deliver_company'=>trim($data['order_deliver_company']),
            'order_deliver_expressid'=>trim($data['order_deliver_expressid']),
            'order_logistics_name'=>trim($data['order_deliver_logistics_name']),
            'order_since_id'=>0
        ]);
        if($res !== false){
            Db("zj_book_order")->where("id = {$data['order_id']}")->update(['status'=>3]);
            Db("goods_order_time")->where("book_order_id = {$data['order_id']}")->update(['order_shipping_time'=>time()]);
            $order_info=Db('user')->alias('u')
                ->join("zj_book_order bo","u.user_id = bo.user_id")
                ->field("bo.user_id,bo.order_sn,bo.book_num,u.user_phone")
                ->where('bo.id','eq',$data['order_id'])->find();
            $book = Db('zj_book')->alias("b")
                ->join("zj_book_order_list bol","b.id=bol.book_id",'left')
                ->where("bol.order_id = {$data['order_id']}")
                ->value("b.title");
            //保存推送消息
            Db('message_order')->insert([
                'merchant_id' => $merchant_id,
                'create_time'=> time(),
                'type'=> 2,
                'order_id'=>0,
                'book_order_id'=>$data['order_id'],
                'user_id'=>$order_info['user_id'],
                'title'=>'订单消息',
                'content'=>'您订阅的'.$book.'等'.$order_info['book_num'].'本绘本已发货，收到货后请及时确认哦',
            ]);
            //发送短信消息
//            $order_deliver_expressid=  $data['order_deliver_expressid'];
//            $order_deliver_logistics_name= $data['order_deliver_company'];
//            $order_sn= '***'.substr($order_info['order_sn'], -6);
//            $usertel=$order_info['user_phone'];
//            $ParamString = "{'ordersn':'$order_sn','logistics':'$order_deliver_logistics_name','expressid':'$order_deliver_expressid'}";
//            $TemplateCode='SMS_120365086';
//            $tool=new Tools();
//            $tool->sendMessage($ParamString, $usertel, '博鸟绘本', $TemplateCode);
        }
        return $res;
    }

    public function saveDeliverStatus($data = []){
        $merchant_id = session('merchant.merchant_id');
        Db("zj_book_order")->where("id = {$data['order_id']}")->update(['status'=>3]);
        Db("goods_order_time")->where("book_order_id = {$data['order_id']}")->update(['order_shipping_time'=>time()]);
        $order_info=Db('user')->alias('u')
            ->join("zj_book_order bo","u.user_id = bo.user_id")
            ->field("bo.user_id,bo.order_sn,bo.book_num,u.user_phone")
            ->where('bo.id','eq',$data['order_id'])->find();
        $book = Db('zj_book')->alias("b")
            ->join("zj_book_order_list bol","b.id=bol.book_id",'left')
            ->where("bol.order_id = {$data['order_id']}")
            ->value("b.title");
        //保存推送消息
        Db('message_order')->insert([
            'merchant_id' => $merchant_id,
            'create_time'=> time(),
            'type'=> 2,
            'order_id'=>0,
            'book_order_id'=>$data['order_id'],
            'user_id'=>$order_info['user_id'],
            'title'=>'订单消息',
            'content'=>'您订阅的'.$book.'等'.$order_info['book_num'].'本绘本已发货，收到货后请及时确认哦',
        ]);
        return true;
    }

    public function pbatchGetSFMailno(){
        $sfd = new SFDeliver();
        $merchant_id = session('merchant.merchant_id');
        $end_time = strtotime(date("Y-m-d")." 23:59:59")+86400*5;
        $deliver_order = Db('zj_book_order')->field("id,address,order_sn,book_num")
            ->where("status = 2 and rent_start_time <= $end_time")
            ->limit(1)
            ->select();
        $export_data = [];
        if(!empty($deliver_order)){
            foreach ($deliver_order as $k => $v){
                $export_data[$k]['order_sn'] = $v['order_sn'];
                $address_info = json_decode($v['address'],true);
                $deliver_order[$k]['user_name'] = $address_info['address_name'];
                $deliver_order[$k]['province'] = $address_info['province'];
                $deliver_order[$k]['city'] = $address_info['city'];
                $deliver_order[$k]['district'] = $address_info['district'];
                $deliver_order[$k]['address'] = $address_info['address'];
                $deliver_order[$k]['mobile'] = $address_info['mobile'];
                $export_data[$k]['user_name'] = $address_info['address_name'];
                $export_data[$k]['user_phone'] = $address_info['mobile'];
                $res = $sfd->createOrder($deliver_order[$k]);
                $data = json_decode($res, true);
                if ($data['status'] == 1) {
                    $deliver_order[$k]['mail_no'] = $data['data']['mailno'];
                    $deliver_order[$k]['err_msg'] = '';
                    $export_data[$k]['mail_no'] = $data['data']['mailno'];
                    $export_data[$k]['err_msg'] = '';
                    //保存发货信息
                    $res = Db("goods_order_deliver")->insert([
                        'order_id'=>'',
                        'book_order_id'=>$v['id'],
                        'order_express_type'=>1,
                        'order_deliver_company'=>'顺丰快递',
                        'order_deliver_expressid'=>trim($data['data']['mailno']),
                        'order_logistics_name'=>'shunfeng',
                        'order_since_id'=>0
                    ]);
                    if($res !== false) {
                        Db("zj_book_order")->where("id = {$v['id']}")->update(['status' => 3]);
                        Db("goods_order_time")->where("book_order_id = {$v['id']}")->update(['order_shipping_time' => time()]);
                        $order_info = Db('user')->alias('u')
                            ->join("zj_book_order bo", "u.user_id = bo.user_id")
                            ->field("bo.user_id,bo.order_sn,bo.book_num,u.user_phone")
                            ->where('bo.id', 'eq', $v['id'])->find();
                        $book = Db('zj_book')->alias("b")
                            ->join("zj_book_order_list bol", "b.id=bol.book_id", 'left')
                            ->where("bol.order_id = {$v['id']}")
                            ->value("b.title");
                        //保存推送消息
                        Db('message_order')->insert([
                            'merchant_id' => $merchant_id,
                            'create_time' => time(),
                            'type' => 2,
                            'order_id' => 0,
                            'book_order_id' => $v['id'],
                            'user_id' => $order_info['user_id'],
                            'title' => '订单消息',
                            'content' => '您订阅的' . $book . '等' . $order_info['book_num'] . '本绘本已发货，收到货后请及时确认哦',
                        ]);
                    }
                } else {
                    if(strpos($data['msg'],'下单失败') !== false){
                        $result = $sfd->searchOrder($deliver_order[$k]);
                        $sdata = json_decode($result, true);
                        if($sdata['status'] == 1){
                            $deliver_order[$k]['mail_no'] = $sdata['data']['mailno'];
                            $deliver_order[$k]['err_msg'] = '';
                            $export_data[$k]['mail_no'] = $sdata['data']['mailno'];
                            $export_data[$k]['err_msg'] = '';
                        }else{
                            $deliver_order[$k]['mail_no'] = '';
                            $deliver_order[$k]['err_msg'] = $sdata['msg'];
                            $export_data[$k]['mail_no'] = '';
                            $export_data[$k]['err_msg'] = $sdata['msg'];
                        }
                    }else{
                        $deliver_order[$k]['mail_no'] = '';
                        $deliver_order[$k]['err_msg'] = $data['msg'];
                        $export_data[$k]['mail_no'] = '';
                        $export_data[$k]['err_msg'] = $data['msg'];
                    }
                }

            }
            return ['deliver_order'=>$deliver_order,'data'=>$export_data];
        }else{
            return false;
        }
    }

    /**
     * 获取待结算订单信息
     */
    public function getJiesuanOrderInfo($order_id){
        $jiesuan_order_info = Db("zj_book_order")->alias("bo")
            ->field("bo.id,u.user_phone,bo.book_num,bo.rent_price,bo.coupon_no,bo.freight,bo.deposit_relief,bo.deposit,bo.pay_type,vc.card_no,c.type,c.rank")
            ->join("zj_user_card vc","bo.user_card_id = vc.id",'left')
            ->join("zj_card c","vc.card_id = c.id",'left')
            ->join("user u","bo.user_id = u.user_id",'left')
            ->where("bo.id = $order_id")
            ->find();
        if(!empty($jiesuan_order_info['coupon_no'])){
            $jiesuan_order_info['coupon_amount'] = Db("zj_coupon_use")->where("coupon_no = '{$jiesuan_order_info['coupon_no']}'")->value('used_amount');
        }else{
            $jiesuan_order_info['coupon_amount'] = 0;
        }
        $books_id = Db("zj_book_order_list")->where("order_id = $order_id")->value("group_concat(book_id)");
        $book_list = Db("zj_book")->field("id,img_medium,title,publisher,isbn13")->where("id in ($books_id)")
            ->order("creater asc,convert(publisher USING gbk) COLLATE gbk_chinese_ci")->select();
        $book = Db("zj_book_order_list")->where("order_id = $order_id")->select();
        foreach ($book as $k => $v){
            if($v['is_purchased'] == 1){
                foreach ($book_list as $bk => $bv){
                    if($bv['id'] == $v['book_id']){
                        $book_list[$bk]['is_sale'] = 1;
                    }
                }
            }
        }
        $jiesuan_order_info['book_list'] = $book_list;
        $jiesuan_order_info['books_id'] = $books_id;
        return $jiesuan_order_info;
    }

    /**
     * 图书订单结算处理
     */
    public function handleJiesuanInfo($data = []){
        $merchant_id = session('merchant.merchant_id');
        $book = new Book();
        $book->startTrans();
        $order = Db("zj_book_order")->where("id = {$data['order_id']}")->find();
        $diff = array_diff(explode(",",$data['books_id']),explode(",",$data['lose_books']));
        $diff = implode(",",$diff);
        if($order['pay_type'] > 0){
            $sres = Db("settlement_bill")->insert([
                'settlement_bill_liquidated' => $data['liquidated'],
                'settlement_bill_damages' => $data['damages'],
                'settlement_bill_total' => $data['total'],
                'settlement_bill_order_id' => 0,
                'settlement_bill_goods_id' => 0,
                'settlement_bill_merchant_id' => $order['merchant_id'],
                'settlement_bill_remarks' => $data['remarks'],
                'book_order_id' => $order['id'],
            ]);
            if($sres !== false){
                $msg='您好，您的绘本订单商家已出具结算单，订单编号为'.$order['order_sn'].',请您查看后及时确认结算单。';
                $msg_order=[
                    'merchant_id' => $merchant_id,
                    'create_time'=> time(),
                    'type'=> 2,
                    'book_order_id'=>$order['id'],
                    'order_id'=>0,
                    'user_id'=>$order['user_id'],
                    'title'=>'订单消息',
                    'content'=>$msg,
                ];
                Db('goods_order_time')->where('book_order_id', 'eq', $order['id'])->update(['order_settlement_bill_generation_time' => time(),]);
                Db('message_order')->insert($msg_order);
//                $modelcode='SMS_120410065';
//                $user_tel=Db('user')
//                    ->alias('a')
//                    ->where('user_id','eq',$order['user_id'])->value('user_phone');
//                $order_sn= '***'.substr($order['order_sn'], -6);
//                $ParamString = "{'ordersn':'$order_sn'}";
//                $tool=new Tools();
//                $tool->sendMessage($ParamString, $user_tel, '博鸟绘本一租介', $modelcode);
            }
        }else{
            $sres = Db("settlement_bill")->insert([
                'settlement_bill_liquidated' => 0,
                'settlement_bill_damages' => 0,
                'settlement_bill_total' => 0,
                'settlement_bill_order_id' => 0,
                'settlement_bill_goods_id' => 0,
                'settlement_bill_merchant_id' => $order['merchant_id'],
                'settlement_bill_remarks' => '',
                'book_order_id' => $order['id'],
            ]);
            if($sres !== false){
                $msg='您好，您的绘本订单商家已出具结算单，订单编号为'.$order['order_sn'].',请您查看后及时确认结算单。';
                $msg_order=[
                    'merchant_id' => $merchant_id,
                    'create_time'=> time(),
                    'type'=> 2,
                    'book_order_id'=>$order['id'],
                    'order_id'=>0,
                    'user_id'=>$order['user_id'],
                    'title'=>'订单消息',
                    'content'=>$msg,
                ];
                Db('goods_order_time')->where('book_order_id', 'eq', $order['id'])->update(['order_settlement_bill_generation_time' => time(),]);
                Db('message_order')->insert($msg_order);
            }
        }
        if(!empty($data['lose_books'])){
            $lose_remark_res = Db("zj_book_order")->where("id = {$data['order_id']}")->update(['lose_remark'=>$data['lose_books']]);
            $lose_stock_res = $book->where("id in ({$data['lose_books']})")->setDec("stock",1);
            $lose_book_list = $book->field("id,stock,surplus_stock")->where("id in ({$data['lose_books']})")->select();
            $stock_data = [];
            if($lose_stock_res !== false && $lose_remark_res !== false){
                foreach ($lose_book_list as $k => $v){
                    $stock_data[] = [
                        'book_id' => $v['id'],
                        'merchant_id' => $merchant_id,
                        'type' => 3,
                        'create_time' => time(),
                        'stock_sub' => 1,
                        'order_id' => $data['order_id'],
                        'total_stock' => $v['stock'],
                        'surplus_stock' => $v['surplus_stock']
                    ];
                    if($v['stock'] == 0){
                        $book->where("id = {$v['id']}")->update(['status'=>3]);
                    }
                }
            }else{
                $book->rollback();
                return false;
            }
            if(!empty($diff)){
                $book_list = $book->field("id,stock,surplus_stock")->where("id in ($diff)")->select();
                $stock_res = $book->where("id in ($diff)")->setInc("surplus_stock",1);
                if($stock_res !== false){
                    foreach ($book_list as $k => $v){
                        $stock_data[] = [
                            'book_id' => $v['id'],
                            'merchant_id' => $merchant_id,
                            'type' => 1,
                            'create_time' => time(),
                            'stock_add' => 1,
                            'order_id' => $data['order_id'],
                            'total_stock' => $v['stock'],
                            'surplus_stock' => $v['surplus_stock']+1
                        ];
                    }
                }else{
                    $book->rollback();
                    return false;
                }
            }
            $history = Db("zj_stock_history")->insertAll($stock_data);
            if($history !== false){
                Db("zj_book_order")->where("id = {$data['order_id']}")->update(['status'=>7]);
                $book->commit();
                return true;
            }else{
                $book->rollback();
            }
        }else{
            $book_list = $book->field("id,stock,surplus_stock")->where("id in ({$data['books_id']})")->select();
            $stock_res = $book->where("id in ({$data['books_id']})")->setInc("surplus_stock",1);
            if($stock_res !== false){
                $stock_data = [];
                foreach ($book_list as $k => $v){
                    $stock_data[] = [
                        'book_id' => $v['id'],
                        'merchant_id' => $merchant_id,
                        'type' => 1,
                        'create_time' => time(),
                        'stock_add' => 1,
                        'order_id' => $data['order_id'],
                        'total_stock' => $v['stock'],
                        'surplus_stock' => $v['surplus_stock']+1
                    ];
                }
                $history = Db("zj_stock_history")->insertAll($stock_data);
                if($history !== false){
                    Db("zj_book_order")->where("id = {$data['order_id']}")->update(['status'=>7]);
                    $book->commit();
                    return true;
                }else{
                    $book->rollback();
                }
            }else{
                $book->rollback();
            }
        }
        return false;
    }

    /**
     * 获取待发货订单信息
     * 修改20180327 新增zj_express_order 左链接，查询快递单号
     * 判断快递公司（后续需要跟进修改）。
     */
    public function getBorrowOrderDetail($order_id){
        $borrow_order_detail = Db("zj_book_order")->alias("bo")
            ->field("bo.lose_remark,bo.is_renew,bo.id,bo.remark,bo.user_card_id,bo.status,bo.pay_type,bo.address_id,bo.address as address_snapshot,bo.pay_amount,bo.coupon_no,bo.deposit_relief,bo.price,bo.rent_price,bo.deposit,bo.freight,bo.order_sn,bo.create_time,bo.rent_start_time,bo.rent_end_time,u.user_phone,bo.book_num,
            ua.address_name,bo.user_message,ua.province,ua.city,ua.district,ua.address,ua.mobile,vc.card_no,c.type,c.rank,
            gd.order_deliver_company,gd.order_deliver_expressid,gd.order_logistics_name,gr.return_expressid,
            gr.return_deliver_company,gr.return_logistics_name,gr.return_revertid,re.revent_tel,
            re.revent_receiver_name,re.revent_province,re.revent_city,re.revent_region,re.revent_detailed_address,
            gt.order_shipping_time,gt.order_delivery_time,gt.order_goods_refund_time, zeo.express_order_id, zeo.express_no ")
            ->join("user_address ua","bo.address_id = ua.id",'left')
            ->join("revert re","bo.revert_id = re.revert_id",'left')
            ->join("goods_order_time gt","gt.book_order_id = bo.id",'left')
            ->join("goods_order_deliver gd","bo.id = gd.book_order_id",'left')
            ->join("goods_order_return gr","bo.id = gr.book_order_id",'left')
            ->join("zj_user_card vc","bo.user_card_id = vc.id",'left')
            ->join("zj_card c","vc.card_id = c.id",'left')
            ->join("user u","bo.user_id = u.user_id",'left')
            ->join("zj_express_order zeo","bo.id = zeo.book_order_id","left")
            ->where("bo.id = $order_id")
            ->find();
        if(!empty($borrow_order_detail['address_snapshot'])){
            $address_info = json_decode($borrow_order_detail['address_snapshot'],true);
            $borrow_order_detail['address_name'] = $address_info['address_name'];
            $borrow_order_detail['province'] = $address_info['province'];
            $borrow_order_detail['city'] = $address_info['city'];
            $borrow_order_detail['district'] = $address_info['district'];
            $borrow_order_detail['address'] = $address_info['address'];
            $borrow_order_detail['mobile'] = $address_info['mobile'];
        }
        if(!empty($borrow_order_detail['coupon_no'])){
            $borrow_order_detail['coupon_use_amount'] = Db("zj_coupon_use")->where("coupon_no = '{$borrow_order_detail['coupon_no']}'")->value("used_amount");
        }else{
            $borrow_order_detail['coupon_use_amount'] = 0;
        }
        $books_id = Db("zj_book_order_list")->where("order_id = $order_id")->value("group_concat(book_id)");
        $book_list = Db("zj_book")->field("id,img_medium,title,publisher,isbn13,price,discount")->where("id in ($books_id)")
            ->order("creater asc,convert(publisher USING gbk) COLLATE gbk_chinese_ci")->select();
        if(!empty($borrow_order_detail['lose_remark'])){
            foreach ($book_list as $k => $v){
                if (strpos($borrow_order_detail['lose_remark'],$v['id'] . "") !== false) {
                    $book_list[$k]['is_lose'] = 1;
                } else {
                    $book_list[$k]['is_lose'] = 0;
                }
            }
        }
        if($borrow_order_detail['is_renew'] == 1){
            $relet_info = Db("zj_book_order")->field("pay_type,pay_amount")->where("origin_id = {$borrow_order_detail['id']}")->find();
            $borrow_order_detail['relet_info'] = $relet_info;
        }
        $borrow_order_detail['book_list'] = $book_list;

        return $borrow_order_detail;
    }

    /**
     * 修改物流公司
     */
    public function editShipCompany($order_id,$company_name,$company_code){
        $res = Db("goods_order_deliver")->where("book_order_id = $order_id")
            ->update(['order_deliver_company'=>trim($company_name),'order_logistics_name'=>trim($company_code)]);
        return $res;
    }

    /**
     * 修改物流单号
     */
    public function editShipExpressid($order_id,$expressid){
        $res = Db("goods_order_deliver")->where("book_order_id = $order_id")
            ->update(['order_deliver_expressid'=>$expressid]);
        return $res;
    }

}